const express = require('express');
const router = express.Router();
const db = require('../utils/db');

//渲染
router.post('/getClass', (req, res) => {
  const { search, page, count } = req.body;
  const sql = `SELECT c_id,c_class,g_grade,COUNT(s_id) AS number FROM p_class LEFT JOIN p_grade ON p_class.c_grade=p_grade.g_id LEFT JOIN p_student ON p_class.c_id=p_student.s_class WHERE c_class LIKE '%${search}%' GROUP BY c_id LIMIT ${
    (page - 1) * count
  },${count};
  `;
  const sql2 = `SELECT COUNT(*) AS total FROM p_class WHERE c_class LIKE '%${search}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ error: 1, msg: err });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ error: 1, msg: err2 });
        } else {
          res.send({
            error: 0,
            msg: '数据获取成功!',
            data: data,
            total: data2[0].total,
          });
        }
      });
    }
  });
});

//添加
router.post('/addClass', (req, res) => {
  const { grade, className } = req.body;
  const sql = `INSERT INTO p_class VALUE(NULL,'${className}',${grade})`;

  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '新增成功' });
  });
});

//删除
router.post('/delClass', (req, res) => {
  const { id } = req.body;
  const sql = `set foreign_key_checks = 0`;
  const sql2 = `DELETE FROM p_class WHERE c_id=${id};`;
  const sql3 = `set foreign_key_checks = 1`;

  db.query(sql, (err, data) => {
    if (err) throw err;
    db.query(sql2, (err2, data2) => {
      if (err2) throw err2;
      db.query(sql3, (err3, data3) => {
        if (err3) throw err3;
        res.send({ error: 0, msg: '删除成功' });
      });
    });
  });
});

//编辑
router.post('/editClass', (req, res) => {
  const { id, className } = req.body;
  const sql = `UPDATE p_class SET c_class='${className}' WHERE c_id=${id};`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '编辑成功' });
  });
});
module.exports = router;
